package Salao;

import Classes.Atendimento;
import Classes.Funcionario;
import Formularios.SalaoApp;
import Formularios.viewBoasVindas;
import Formularios.viewCadastroFunc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import javax.swing.JOptionPane;

public class Salao {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    //static final String DATABASE_URL = "jdbc:mysql://localhost/petsistemas";

    public boolean existeFuncionario(String CPF) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select idFuncionario from funcionario where cpf = '" + CPF + "'");
            while (resultset.next()) {
                return true;
            }
			
        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }
        return false;
    }

    public ResultSet consultaCliente() {
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();
            resultset = statement.executeQuery("select * from cliente join pessoa on idcliente = idPessoa");
            return resultset;

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }//consultaCliente

    public ResultSet consultaAtendimento() {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select at.idatendimento, DATE_FORMAT(at.dataHora, '%d/%m/%Y'),TIME(at.dataHora), pFunc.Nome from atendimento at join funcionario f on f.idFuncionario = at.idFunc join cliente c on c.idCliente = at.idCliente join pessoa pFunc on pFunc.idPessoa = f.idFuncionario where at.situacao = 1");

            metaData = resultset.getMetaData();

            return resultset;


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }
    
    public ResultSet consultaAtendimentos() {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select at.idatendimento, DATE_FORMAT(at.dataHora, '%d/%m/%Y'),TIME(at.dataHora), pFunc.Nome, at.valor, at.observacao, at.duracao, at.situacao, pCli.Nome, f.codServico from atendimento at join funcionario f on f.idFuncionario = at.idFunc join cliente c on c.idCliente = at.idCliente join pessoa pFunc on pFunc.idPessoa = f.idFuncionario join pessoa pCli on pCli.idPessoa = c.idCliente");

            metaData = resultset.getMetaData();

            return resultset;


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }

    public ResultSet consultaDadosAtendimento(String dataHora, String nomeFunc) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select * from atendimento at join funcionario f on at.idFunc = f.idFuncionario join pessoa p on p.idPessoa = f.idFuncionario where p.Nome = '" + nomeFunc + "' and at.dataHora = '" + dataHora + "'");

            metaData = resultset.getMetaData();

            return resultset;


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }

    public int atualizarAtendimento(int id, float valor, String obs) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;
        int codServico = 2;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            int n = statement.executeUpdate("UPDATE atendimento SET valor = '" + valor + "', observacao='" + obs + "' WHERE idAtendimento='" + id + "'");
            return n;

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return 0;

    }

    public int atualizarSituacaoAtendimento(int id, int situacao) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;
        int codServico = 2;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            int n = statement.executeUpdate("UPDATE atendimento SET situacao = '" + situacao + "' WHERE idAtendimento='" + id + "'");
            return n;

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return 0;

    }

    public int buscarIdFuncionario(String cpf) {

        int idFuncionario = 0;
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select * from funcionario where funcionario.cpf='" + cpf + "'");
            metaData = resultset.getMetaData();

            while (resultset.next()) {
                return Integer.parseInt(resultset.getString(1));
            }

            resultset.close();
        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return idFuncionario;
    }

    public void excluirFuncionario(int id) {
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            int n = statement.executeUpdate("delete from funcionario where idFuncionario ='" + id + "'");
            int j = statement.executeUpdate("delete from pessoa where idPessoa ='" + id + "'");


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

    }

    public int buscarIdServico(String serv) {

        int idServico = 0;
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select * from servico where servico.nome='" + serv + "'");
            metaData = resultset.getMetaData();

            while (resultset.next()) {

                return Integer.parseInt(resultset.getString(1));
            }

            resultset.close();
        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return idServico;
    }

    public ResultSet consultaFuncionario() {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select * from pessoa join funcionario on pessoa.idPessoa = funcionario.idFuncionario");

            metaData = resultset.getMetaData();

            return resultset;


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }

    public ResultSet consultaServico() {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            resultset = statement.executeQuery("select * from servico");

            metaData = resultset.getMetaData();
            return resultset;

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return resultset;
    }

    public int atualizarFuncionario(String nome, String telefone, String endereco, String CPF,
            String CTPS, float salario, int servico, int id) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            int n = statement.executeUpdate("UPDATE funcionario SET cpf ='" + CPF + "', ctps ='" + CTPS + "',endereco ='" + endereco + "', salario ='" + salario + "', codServico ='" + servico + "' WHERE idfuncionario='" + id + "'");
            int j = statement.executeUpdate("UPDATE pessoa SET Nome ='" + nome + "', Telefone ='" + telefone + "'WHERE idPessoa='" + id + "'");
            return n;

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return 0;

    }

    public void cadastrarFuncionario(String nome, String telefone, String endereco, String CPF,
            String CTPS, float salario, int servico) {

        Funcionario func = new Funcionario(nome, telefone, endereco, CPF, CTPS, salario, servico);

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        Object objPessoa;
        int idPessoa;

        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            ResultSet resultset = null;
            ResultSetMetaData metaData = null;

            int sucesso = statement.executeUpdate("insert into pessoa(Nome, Telefone) values('" + nome + "','" + telefone + "')");
            if (sucesso != 1) {
                SalaoApp.mostraMensagem("Erro ao inserir Funcionario", "Cadatrar Funcionário");

            } else {
                resultset = statement.executeQuery("select pessoa.idPessoa from pessoa where pessoa.nome ='" + nome + "'and pessoa.telefone ='" + telefone + "'");
                metaData = resultset.getMetaData();

                if (resultset.next()) {
                    objPessoa = resultset.getObject(1);
                    idPessoa = Integer.parseInt(objPessoa.toString());
                    sucesso = statement.executeUpdate("insert into funcionario(idFuncionario, cpf, ctps, endereco, salario, codServico) values('" + idPessoa + "','" + CPF + "','" + CTPS + "','" + endereco + "','" + salario + "' , '" + servico + "')");
                    if (sucesso == 1) {
                        SalaoApp.mostraMensagem("Funcionário Cadastrado!", "Cadatrar Funcionário");
                    } else {
                        SalaoApp.mostraMensagem("Erro ao inserir Funcionario", "Cadatrar Funcionário");
                    }
                } else {
                    SalaoApp.mostraMensagem("Erro ao inserir Funcionario", "Cadatrar Funcionário");
                }
            }

            resultset.close();

        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }
    }

    public List<String> consultaHorario(String dia, String cpf) {

        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        ResultSetMetaData metaData = null;
        ResultSet func;
        int x = 0;
        int dur = 0;
        String select = null;

        SimpleDateFormat hora = new SimpleDateFormat("HH:mm");
        List<String> horario = new ArrayList<String>();
        for (int i = 0; i < 20; i++) {
            horario.add(Atendimento.horarios[i]);
        }
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();

            func = consultaFuncionario();
            while (func.next()) {
                if (cpf.toString().equals(func.getString(5).toString())) {
                    x = Integer.parseInt(func.getString(1).toString());
                    break;
                }

            }

            int cont = 0;
            resultset = statement.executeQuery("select * from atendimento where idFunc = '" + x + "' and DATE_FORMAT(dataHora,'%Y-%m-%d') = '" + dia + "' and situacao = 1");
            // verificar se existe linha tratar caso não exista.
            while (resultset.next()) {
                cont++;
            }
            if (cont == 0) {
                return horario;
            } else {
                resultset = statement.executeQuery("select * from atendimento where idFunc = '" + x + "' and DATE_FORMAT(dataHora,'%Y-%m-%d') = '" + dia + "' and situacao = 1");
                while (resultset.next()) {
                    for (int i = 0; i < 20; i++) {
                        for (int j = 0; j < horario.size(); j++) {
                            if (Atendimento.horarios[i].toString().equals(hora.format(resultset.getTime(2)).toString())) {
                                select = hora.format(resultset.getTime(2)).toString();
                                horario.remove(j);
                                dur = resultset.getInt(5);
                                break;
                            }//if
                        }
                    }//for
                    //valedar duraçao antes de retornar
                }//while
                resultset = statement.executeQuery("select * from atendimento where idFunc = '" + x + "' and DATE_FORMAT(dataHora,'%Y-%m-%d') = '" + dia + "' and situacao = 1");
                while (resultset.next()) {
                    for (int i = 0; i < cont; i++) {
                        for (int j = 0; j < horario.size(); j++) {
                            int c = formataDuracao(select, 0); //piso
                            int a = formataDuracao(select, dur);
                            int b = formataDuracao(horario.get(j), 0);

                            if (a > b && b > c) {
                                horario.remove(j);
                            }
                        }//for
                    } //for
                }//while
            }//else
            metaData = resultset.getMetaData();

            return horario;


        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }

        return horario;
    }
    
     public int formataDuracao(String hora, int dur){
        int a = Integer.parseInt(hora.substring(0, 2));
        int b = Integer.parseInt(hora.substring(3, 5));
        return a*60 + b +dur;
    }
    
    public boolean validaHorario(String hora, int dur, String data){
        int a = formataDuracao(hora, dur);
     
        int piso = formataDuracao(hora, 0);
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset = null;
        int c = 0;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();
            
            resultset = statement.executeQuery("select * from atendimento where situacao = 1 and DATE_FORMAT(dataHora,'%Y-%m-%d') = '"+data+"'");
            while(resultset.next()){
                c = formataDuracao(resultset.getString(2).substring(11, 16), 0);
                if(piso <= c && c <= a){
                    return false;
                }
            }
            return true;
        }catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }
        return true;
    }
    
    public void cadastrarAtendimento(String data, String hora, int valor, String obs, int dur, String cliente, String func)
    {
       /* Atendimento - instanciar
         chamar função para cadastrar*/  
        String DATABASE_URL = "jdbc:mysql://localhost/salao";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultset;
        int funcionario = 0;
        int client = 0;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DATABASE_URL, "root", "root");

            statement = connection.createStatement();
            resultset = statement.executeQuery("select * from funcionario where cpf = '"+func.substring(0, 14) +"'");
            while(resultset.next()){
                funcionario = resultset.getInt(1);
            }
            resultset = statement.executeQuery("select * from cliente join pessoa on idcliente = idPessoa where Telefone = '"+cliente.substring(0, 14) +"'");
            while(resultset.next()){
                client = resultset.getInt(1);
            }
            int suc = statement.executeUpdate("insert into atendimento(dataHora, valor, observacao, duracao, situacao, idCliente, idFunc) values('"+data+" "+hora+"', '"+valor+"', '"+obs+"', '"+dur+"', '1', '"+client+"', '"+funcionario+"')");    
        if(suc ==1){
            JOptionPane.showMessageDialog(null, "Atendimento agendado com sucesso!");
        }
        else
            JOptionPane.showMessageDialog(null, "Não foi possivel agendar o atendimmento!");
        } catch (Exception exception) {
            exception.printStackTrace();
            System.exit(1);
        }
    }
}
